import uuid
from datetime import datetime
import mysql.connector
from mysql.connector import pooling
import logging

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    filename='trading_system.log'
)
logger = logging.getLogger(__name__)

class TradeSystem:
    def __init__(self, db_config):
        """
        初始化交易系统
        :param db_config: 数据库配置字典
        """
        self.db_config = db_config
        self.connection_pool = None
        self._init_db_pool()
    
    def _init_db_pool(self):
        """初始化数据库连接池"""
        try:
            self.connection_pool = pooling.MySQLConnectionPool(
                pool_name="trading_pool",
                pool_size=5,
                **self.db_config
            )
            logger.info("数据库连接池初始化成功")
        except Exception as e:
            logger.error(f"数据库连接池初始化失败: {str(e)}")
            raise
    
    def get_connection(self):
        """从连接池获取连接"""
        try:
            return self.connection_pool.get_connection()
        except Exception as e:
            logger.error(f"获取数据库连接失败: {str(e)}")
            raise
    
    def create_order(self, user_id, product_id, direction, offset_flag, price, volume):
        """
        创建交易订单
        :param user_id: 用户ID
        :param product_id: 产品ID
        :param direction: 方向 (BUY/SELL)
        :param offset_flag: 开平标志 (OPEN/CLOSE)
        :param price: 价格
        :param volume: 数量
        :return: 订单ID
        """
        order_id = str(uuid.uuid4())
        conn = None
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 插入订单记录
            insert_order_sql = """
            INSERT INTO trade_orders (
                order_id, user_id, product_id, direction, offset_flag, 
                price, volume, status
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, 'PENDING')
            """
            cursor.execute(insert_order_sql, (
                order_id, user_id, product_id, direction, 
                offset_flag, price, volume
            ))
            
            conn.commit()
            logger.info(f"成功创建订单: {order_id}")
            return order_id
            
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"创建订单失败: {str(e)}")
            raise
        finally:
            if conn:
                conn.close()
    
    def record_trade(self, order_id, price, volume):
        """
        记录成交
        :param order_id: 订单ID
        :param price: 成交价格
        :param volume: 成交数量
        :return: 成交记录ID
        """
        record_id = str(uuid.uuid4())
        conn = None
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 插入成交记录
            insert_record_sql = """
            INSERT INTO trade_records (
                record_id, order_id, price, volume
            ) VALUES (%s, %s, %s, %s)
            """
            cursor.execute(insert_record_sql, (
                record_id, order_id, price, volume
            ))
            
            # 更新订单状态和已成交量
            update_order_sql = """
            UPDATE trade_orders 
            SET traded_volume = traded_volume + %s,
                status = CASE 
                    WHEN volume = traded_volume + %s THEN 'FILLED'
                    ELSE 'PARTIAL'
                END
            WHERE order_id = %s
            """
            cursor.execute(update_order_sql, (volume, volume, order_id))
            
            conn.commit()
            logger.info(f"成功记录成交: {record_id} 订单: {order_id}")
            return record_id
            
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"记录成交失败: {str(e)}")
            raise
        finally:
            if conn:
                conn.close()
    
    def update_position(self, user_id, product_id, direction, price, volume):
        """
        更新持仓
        :param user_id: 用户ID
        :param product_id: 产品ID
        :param direction: 方向 (LONG/SHORT)
        :param price: 价格
        :param volume: 数量
        """
        conn = None
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 检查是否已有持仓
            check_sql = """
            SELECT position_id, direction, avg_price, volume 
            FROM positions 
            WHERE user_id = %s AND product_id = %s
            """
            cursor.execute(check_sql, (user_id, product_id))
            position = cursor.fetchone()
            
            if position:
                # 已有持仓，更新
                position_id, existing_direction, avg_price, existing_volume = position
                
                if existing_direction == direction:
                    # 同方向持仓，计算新均价
                    new_volume = existing_volume + volume
                    new_avg_price = (existing_volume * avg_price + volume * price) / new_volume
                    
                    update_sql = """
                    UPDATE positions 
                    SET avg_price = %s, volume = %s, update_time = NOW()
                    WHERE position_id = %s
                    """
                    cursor.execute(update_sql, (new_avg_price, new_volume, position_id))
                else:
                    # 反方向持仓，对冲
                    if volume >= existing_volume:
                        # 完全对冲，删除持仓
                        delete_sql = "DELETE FROM positions WHERE position_id = %s"
                        cursor.execute(delete_sql, (position_id,))
                    else:
                        # 部分对冲，减少持仓量
                        update_sql = """
                        UPDATE positions 
                        SET volume = %s, update_time = NOW()
                        WHERE position_id = %s
                        """
                        cursor.execute(update_sql, (existing_volume - volume, position_id))
            else:
                # 新建持仓
                position_id = str(uuid.uuid4())
                insert_sql = """
                INSERT INTO positions (
                    position_id, user_id, product_id, direction, 
                    avg_price, volume
                ) VALUES (%s, %s, %s, %s, %s, %s)
                """
                cursor.execute(insert_sql, (
                    position_id, user_id, product_id, direction,
                    price, volume
                ))
            
            conn.commit()
            logger.info(f"成功更新持仓: 用户 {user_id} 产品 {product_id}")
            
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"更新持仓失败: {str(e)}")
            raise
        finally:
            if conn:
                conn.close()
    
    def get_user_positions(self, user_id):
        """
        获取用户持仓
        :param user_id: 用户ID
        :return: 持仓列表
        """
        conn = None
        try:
            conn = self.get_connection()
            cursor = conn.cursor(dictionary=True)
            
            query = """
            SELECT p.position_id, p.product_id, pr.symbol, pr.name, 
                   p.direction, p.avg_price, p.volume, p.create_time
            FROM positions p
            JOIN products pr ON p.product_id = pr.product_id
            WHERE p.user_id = %s
            """
            cursor.execute(query, (user_id,))
            return cursor.fetchall()
            
        except Exception as e:
            logger.error(f"获取持仓失败: {str(e)}")
            raise
        finally:
            if conn:
                conn.close()
    
    def get_user_orders(self, user_id, status=None):
        """
        获取用户订单
        :param user_id: 用户ID
        :param status: 订单状态 (可选)
        :return: 订单列表
        """
        conn = None
        try:
            conn = self.get_connection()
            cursor = conn.cursor(dictionary=True)
            
            query = """
            SELECT o.order_id, o.product_id, pr.symbol, pr.name, 
                   o.direction, o.offset_flag, o.price, o.volume, 
                   o.traded_volume, o.status, o.order_time
            FROM trade_orders o
            JOIN products pr ON o.product_id = pr.product_id
            WHERE o.user_id = %s
            """
            
            if status:
                query += " AND o.status = %s"
                cursor.execute(query, (user_id, status))
            else:
                cursor.execute(query, (user_id,))
                
            return cursor.fetchall()
            
        except Exception as e:
            logger.error(f"获取订单失败: {str(e)}")
            raise
        finally:
            if conn:
                conn.close()

# 示例用法
if __name__ == "__main__":
    # 数据库配置 (从配置文件读取更安全)
    db_config = {
        "host": "localhost",
        "user": "your_username",
        "password": "your_password",
        "database": "trade_monitor_v2"
    }
    
    # 初始化交易系统
    trade_system = TradeSystem(db_config)
    
    # 示例: 创建订单
    try:
        order_id = trade_system.create_order(
            user_id="user123",
            product_id="product456",
            direction="BUY",
            offset_flag="OPEN",
            price=100.50,
            volume=10
        )
        print(f"订单创建成功: {order_id}")
    except Exception as e:
        print(f"订单创建失败: {str(e)}")
    
    # 示例: 记录成交
    try:
        record_id = trade_system.record_trade(
            order_id=order_id,
            price=100.45,
            volume=5
        )
        print(f"成交记录成功: {record_id}")
    except Exception as e:
        print(f"成交记录失败: {str(e)}")
    
    # 示例: 更新持仓
    try:
        trade_system.update_position(
            user_id="user123",
            product_id="product456",
            direction="LONG",
            price=100.45,
            volume=5
        )
        print("持仓更新成功")
    except Exception as e:
        print(f"持仓更新失败: {str(e)}")
    
    # 示例: 查询持仓
    try:
        positions = trade_system.get_user_positions("user123")
        print("用户持仓:", positions)
    except Exception as e:
        print(f"查询持仓失败: {str(e)}")
    
    # 示例: 查询订单
    try:
        orders = trade_system.get_user_orders("user123", "PENDING")
        print("用户订单:", orders)
    except Exception as e:
        print(f"查询订单失败: {str(e)}")